-- @owner: opentestcase027
-- @date: 2022/6/16
-- @testpoint:count结合concat
--step1: 建表     expect: 建表成功
drop table if exists t_ustore_concat_0030 cascade;
create table t_ustore_concat_0030
(
    hd_demo_sk                integer               not null,
    hd_income_band_sk         integer                       ,
    hd_buy_potential          char(15)                      ,
    hd_dep_count              integer                       ,
    hd_vehicle_count          integer
 ) WITH (STORAGE_TYPE=USTORE);
--step2: 创建序列     expect: 成功
drop sequence if exists s_avg_seq;
create sequence s_avg_seq increment by 1 start with 1;
--step3: 数据准备     expect: 成功
begin
	for i in 1..1000 loop
	insert into t_ustore_concat_0030 values(s_avg_seq.nextval,2,'0-500          ',0,0);
    end loop;
end;
/
--step4: 创建索引   expect:成功
drop index if exists i_ustore_concat_0030 ;
create index i_ustore_concat_0030 on t_ustore_concat_0030 (hd_demo_sk);
--step5: count结合concat   expect:成功
select
count(concat(
case when (select count(concat(hd_income_band_sk,hd_buy_potential)) from t_ustore_concat_0030  where hd_demo_sk between 1 and 20) > 2
     then (select avg(hd_vehicle_count)  from t_ustore_concat_0030  where hd_demo_sk between 1 and 20000)  end ,
case when (select count(concat(hd_income_band_sk,hd_buy_potential))  from t_ustore_concat_0030  where hd_demo_sk between 21 and 40) > 2
     then (select avg(hd_vehicle_count)  from t_ustore_concat_0030 where hd_demo_sk between 20000 and 40000)
end   ))
from t_ustore_concat_0030;
--step6: 清理环境   expect: 清理成功
drop table if exists t_ustore_concat_0030 cascade;

